﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace BLL
{
    public class StaffOfWardBLL
    {
        HospitalDataContext DB = new HospitalDataContext();

        public string layjob(string id)
        {
            return DB.Jobs.Where(s => s.StaffID == id && s.FinishDate == null).Select(k => k.JobID).Max().ToString();
        }
        public string Insert(StaffOfWardDTO sow)
        {
            try
            {
                
                StaffOfWard sw = new StaffOfWard();
                string job = DB.Jobs.Where(s => s.StaffID == sow.StaffID && s.FinishDate == null).Select(k => k.JobID).Max().ToString();
                sw.JobID = job;
                sw.WardID= sow.WardId;
                sw.StartDate = sow.StartDate;

                DB.StaffOfWards.InsertOnSubmit(sw);
                DB.SubmitChanges();
                return "success";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public IQueryable ListStaffInWard(string wardid)
        {
            return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.JobID,
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    d.Position1,

                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    Position=e.Position1,
                    f.WardID,
                    f.FinishDate
                }
                ).Where(s=>s.WardID==wardid && s.FinishDate==null).Distinct().AsQueryable();
        }

        public IQueryable ListStaffInWardDetails(string wardid)
        {
            return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    a.Sex,
                    a.DateOfBirth,
                    a.NIN,
                    a.ImageProfile,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.JobID,
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    c.Sex,
                    c.DateOfBirth,
                    c.NIN,
                    c.ImageProfile,
                    d.Position1

                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    e.Sex,
                    e.DateOfBirth,
                    e.NIN,
                    e.ImageProfile,
                    Position = e.Position1,
                    f.WardID,
                    f.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.FinishDate ==null && !(from p in DB.Allocateds where p.WardID==wardid && p.Status=="Work" select p.StaffID).Contains(s.StaffID)).Distinct().AsQueryable();
        }
        public static bool IsNumber(string value)
        {
            // Return true if this is a number.
            int number1;
            return int.TryParse(value, out number1);
        }
        public IQueryable SearchIDName(string wardid,string key)
        {
            int asa = 5;

            if (key.Length == 4 && key.ToCharArray()[0].ToString().ToUpper().Equals("S") && IsNumber(key.Substring(1, 3)) && (Convert.ToInt32(key.Substring(1, 3)).GetType() == asa.GetType()))
            {
                return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    a.Sex,
                    a.DateOfBirth,
                    a.NIN,
                    a.ImageProfile,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.JobID,
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    c.Sex,
                    c.DateOfBirth,
                    c.NIN,
                    c.ImageProfile,
                    d.Position1,

                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    e.Sex,
                    e.DateOfBirth,
                    e.NIN,
                    e.ImageProfile,
                    Position = e.Position1,
                    f.WardID,
                    f.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null && s.StaffID==key).Distinct().AsQueryable();
            }
            else
            {
                return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    a.Sex,
                    a.DateOfBirth,
                    a.NIN,
                    a.ImageProfile,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.JobID,
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    c.Sex,
                    c.DateOfBirth,
                    c.NIN,
                    c.ImageProfile,
                    d.Position1,

                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    e.Sex,
                    e.DateOfBirth,
                    e.NIN,
                    e.ImageProfile,
                    Position = e.Position1,
                    f.WardID,
                    f.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null && s.Name.Contains(key)).Distinct().AsQueryable();
            }
        }
        public DataSet ToDataTable(string wardid)
        {
           var query= DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.JobID,
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    d.Position1,

                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    Position = e.Position1,
                    f.WardID,
                    f.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null).Distinct().AsQueryable();
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }

            IDbCommand cmd = DB.GetCommand(query as IQueryable);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = (SqlCommand)cmd;
            DataTable dt = new DataTable();
            //adapter.Fill(ds);

            try
            {
                cmd.Connection.Open();
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            finally
            {
                cmd.Connection.Close();
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            
            return ds;
        }

        public IQueryable WardStaffList(string wardid)
        {
            return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    b.PositionID,
                    b.FinishDate
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    d.Position1,
                    c.FinishDate

                }
                ).Join(
                DB.Allocateds,
                e => e.StaffID,
                f => f.StaffID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    Position = e.Position1,
                    f.WardID,
                    f.Shift,
                    f.Status,
                    f.DateBegin,
                    e.FinishDate
                }
                ).Join(
                DB.Wards,
                g => g.WardID,
                h => h.WardID,
                (g, h) => new
                {
                    g.StaffID,
                    g.Name,
                    g.Address,
                    g.Tel,
                    g.Position,
                    g.Shift,
                    g.Status,
                    g.DateBegin,
                    h.WardID,
                    h.WardName,
                    h.Location,
                    h.TelExtn,
                    g.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.Status == "Work" && s.FinishDate ==null).Distinct().AsQueryable();
        }
        public IQueryable WardStaffList2(string wardid, DateTime datebegin)
        {
            return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    b.PositionID,
                    b.FinishDate
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    d.Position1,
                    c.FinishDate

                }
                ).Join(
                DB.Allocateds,
                e => e.StaffID,
                f => f.StaffID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    Position = e.Position1,
                    f.WardID,
                    f.Shift,
                    f.Status,
                    f.DateBegin,
                    e.FinishDate
                }
                ).Join(
                DB.Wards,
                g => g.WardID,
                h => h.WardID,
                (g, h) => new
                {
                    g.StaffID,
                    g.Name,
                    g.Address,
                    g.Tel,
                    g.Position,
                    g.Shift,
                    g.Status,
                    g.DateBegin,
                    h.WardID,
                    h.WardName,
                    h.Location,
                    h.TelExtn,
                    g.FinishDate
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null && s.DateBegin == datebegin).Distinct().AsQueryable();
        }
        public string[] WardChargeNurseInfo(string wardid)
        {
            var query = DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    b.JobID,
                    b.PositionID,
                    FinishDateJob = b.FinishDate
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.JobID,
                    c.FinishDateJob,
                    d.Position1
                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Position1,
                    e.FinishDateJob,
                    f.WardID,
                    f.FinishDate
                }
                ).Join(
                DB.Wards,
                g => g.WardID,
                h => h.WardID,
                (g, h) => new
                {
                    g.StaffID,
                    g.Name,
                    g.Position1,
                    g.FinishDateJob,
                    g.FinishDate,
                    h.WardID
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null && s.FinishDateJob == null && s.Position1 == "Charge Nurse").Distinct().AsQueryable().FirstOrDefault();
            string[] info = new string[2];
            info[0] = query.StaffID;
            info[1] = query.Name;
            return info;
        }
        public DataSet DataSourseReport(string wardid)
        {
            var query2 = DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    b.JobID,
                    b.PositionID,
                    FinishDateJob = b.FinishDate
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.JobID,
                    c.FinishDateJob,
                    d.Position1
                }
                ).Join(
                DB.StaffOfWards,
                e => e.JobID,
                f => f.JobID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Position1,
                    e.FinishDateJob,
                    f.WardID,
                    f.FinishDate
                }
                ).Join(
                DB.Wards,
                g => g.WardID,
                h => h.WardID,
                (g, h) => new
                {
                    StaffID1 = g.StaffID,
                    Name1 = g.Name,
                    Position1 = g.Position1,
                    g.FinishDateJob,
                    g.FinishDate,
                    h.WardName,
                    h.WardID,
                    h.Location,
                    h.TelExtn
                }
                ).Where(s => s.WardID == wardid && s.FinishDate == null && s.FinishDateJob == null && s.Position1 == "Charge Nurse").Distinct().AsQueryable();

            IDbCommand cmd2 = DB.GetCommand(query2 as IQueryable);
            SqlDataAdapter adapter2 = new SqlDataAdapter();
            adapter2.SelectCommand = (SqlCommand)cmd2;
            DataTable dt2 = new DataTable();
            //adapter.Fill(ds);

            try
            {
                cmd2.Connection.Open();
                adapter2.FillSchema(dt2, SchemaType.Source);
                adapter2.Fill(dt2);
            }
            finally
            {
                cmd2.Connection.Close();
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(dt2);
            var query1 = DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    b.JobID,
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Tel,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Tel,
                    d.Position1,

                }
                ).Join(
                DB.Allocateds,
                e => e.StaffID,
                f => f.StaffID,
                (e, f) => new
                {
                    e.StaffID,
                    e.Name,
                    e.Address,
                    e.Tel,
                    Position = e.Position1,
                    WardID1=f.WardID,
                    f.Shift,
                    f.Status,
                    f.DateBegin
                }
                ).Where(s => s.WardID1 == wardid && s.Status == "Work").Distinct().AsQueryable();
            if (query1 == null)
            {
                throw new ArgumentNullException("query1");
            }

            IDbCommand cmd = DB.GetCommand(query1 as IQueryable);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = (SqlCommand)cmd;
            DataTable dt1 = new DataTable();
            //adapter.Fill(ds);

            try
            {
                cmd.Connection.Open();
                adapter.FillSchema(dt1, SchemaType.Source);
                adapter.Fill(dt1);
            }
            finally
            {
                cmd.Connection.Close();
            }
            
            ds.Tables.Add(dt1);

            

            return ds;
        }
    }
}
